package com.hnkywl.jip.dao;

import net.ruixin.orm.hibernate.Page;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.springframework.stereotype.Repository;

import java.sql.*;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

/**
 * Created with IntelliJ IDEA.
 * User: jerry
 * Date: 13-2-26
 * Time: 下午2:02
 * To change this template use File | Settings | File Templates.
 */
@Repository
public class CashAcceptDao extends SimpleHibernateTemplate {

    public Page<Object[]> findCashAccept(Integer companyId, Integer status, String voucherId, String operator, Integer limit, Integer start) {
        // sql查询字段
        String sqlColumn = "select a.compid,compname=dbo.gf_compid_shortname(a.compid)," +
                "a.billid,pjno=dbo.gf_cw_pjnos(a.billid),inctypename=(case when a.inctype=5 then '银票' when a.inctype=6 then '商票' end)," +
                "a.type,typename=(case when a.type=2 then '票据贴现' when a.type=3 then '票据承兑' end)," +
                "je=b.je,zddt=convert(varchar(10),a.zddt,102),zdr=dbo.gf_manid_name(a.zdr),a.jiuqi_voucher_num,isins=a.u8insflag," +
                "isdel=(case when a.u8insflag=3 and a.u8delflag=1 then 1 else 0 end)," +
                "isignseq=isnull(isignseq,0),iperiod=isnull(iperiod,0)," +
                "isreset=(case when a.u8insflag=2 or a.u8delflag=2 then 1 else 0 end),a.u8cnt," +
                "operation=(case when :status in (1,4,5,9) then 'add' when :status=7 then 'delete' when  :status in (6,8) then 'reset' else 'none' end)" +
                " from t_cw_pj_bill a inner join (select a.billid,je=sum(c.je) " +
                " from t_cw_pj_bill a inner join t_cw_pj_bill_dtl b on a.billid=b.billid " +
                " inner join t_cw_pj c on b.pjid=c.pjid " +
                " left join t_crm_man d on a.zdr=d.manid " +
                (operator != null && !operator.isEmpty() ? " where  d.name like :operator " : "") +
                " group by a.billid) b on a.billid=b.billid " +
                " where ((:status in (1,2,3) and a.u8insflag=:status and isnull(a.jiuqi_insert_flag,1)=1) " +
                " or (:status=4 and a.u8delflag=2) " +
                " or (:status=5 and a.u8delflag=3)" +
                " or (:status in(6,7) and a.jiuqi_insert_flag=:status-4)" +
                " or (:status=8 and a.jiuqi_delete_flag=2)" +
                " or (:status=9 and a.jiuqi_delete_flag=3)) " +
                (voucherId != null && !voucherId.isEmpty() ? " and a.jiuqi_voucher_num=:voucherId " : "") +
                (companyId != null && companyId != 0 ? " and  a.compid=:compid" : "");
        //查询记录数
        String sqlCount = "select count(1) from (" + sqlColumn + ") as count";
        //获取总记录数
        SQLQuery query = createSQLQuery(sqlCount);

        if (companyId != null && companyId != 0)
            query.setInteger("compid", companyId);
        if (voucherId != null && !voucherId.isEmpty())
            query.setString("voucherId", voucherId);
        if (operator != null && !operator.isEmpty())
            query.setString("operator", "%" + operator + "%");
        query.setInteger("status", status);
        Integer total = (Integer) query.uniqueResult();
        List<Object[]> result = null;

        if (total > 0) {
            //获取列表数据
            query = createSQLQuery(sqlColumn);
            if (companyId != null && companyId != 0)
                query.setInteger("compid", companyId);
            if (voucherId != null && !voucherId.isEmpty())
                query.setString("voucherId", voucherId);
            if (operator != null && !operator.isEmpty())
                query.setString("operator", "%" + operator + "%");
            query.setInteger("status", status);
            result = query.setFirstResult(start).setMaxResults(limit).list();
        }
        //返回值
        Page<Object[]> page = new Page<Object[]>();
        page.setTotalCount(total);
        if (result != null && !result.isEmpty())
            page.setResult(result);
        return page;
    }


    public List getVoucherMaster(Integer id) {
        String sql = "select dbo.gf_cw_sys_csign(dbo.gf_sys_cwcompid_by_compid(c.company_id)," +
                "dbo.gf_cw_sys_isignseq(dbo.gf_sys_cwcompid_by_compid(c.company_id),b.jiuqitype))," +
                "c.company_name,c.company_code from t_cw_bank b,t_jiuqi_company c,t_cw_pj_bill p " +
                "where p.bankid=b.bankid and dbo.gf_sys_cwcompid_by_compid(p.compid)=c.company_id and p.billid=?";
        Object[] result = (Object[]) createSQLQuery(sql, id).uniqueResult();
        if (result != null)
            return Arrays.asList(result);
        return null;
    }

    public List getVoucher(Integer id) {
        String sql = "{call sp_jiuqi_voucher_note(?,?,?)}";
        List<Object[]> list = createSQLQuery(sql, id, null, null).list();
        return list;
    }

    public List getRevokeInfo(Integer id) {
        String sql = "select p.jiuqi_uuid,p.jiuqi_voucher_id,c.company_code,p.jiuqi_acctyear from t_cw_pj_bill p,t_jiuqi_company c where p.compid=c.company_id and p.billid=?";
        Object[] result = (Object[]) createSQLQuery(sql, id).uniqueResult();
        if (result != null)
            return Arrays.asList(result);
        return null;
    }

    public String revokeNote(Integer id, Integer revokeType, Integer userId) throws SQLException {
        Session session = getSession();
        Connection conn = session.connection();
        CallableStatement call = conn.prepareCall("{call sp_jiuqi_note_revoke(?,?,?,?)}");
        call.setInt(1, revokeType);
        call.setInt(2, userId);
        call.setInt(3, id);
        call.registerOutParameter(4, Types.VARCHAR);
        call.execute();
        return call.getString(4);
    }
}
